﻿CREATE PROCEDURE [megasys].[s_Techician_Tracker_InHands]
AS SET NOCOUNT ON
DECLARE @TechID varchar(18),@TrackerID varchar(8000),@SQL varchar(512)
DECLARE cur CURSOR LOCAL READ_ONLY FOR

SELECT T.ID, M.TrackerID FROM megasys.Martha M
INNER JOIN t_Technician T ON ISNULL(NULLIF(M.Dealer,''),M.Installer) = T.FirstName
WHERE M.Status = 'DEALER' AND NOT M.TrackerID IS NULL
UNION
SELECT T.ID, M.TrackerID FROM megasys.Martha M
INNER JOIN t_Technician T ON M.Installer = T.FirstName
WHERE M.Status = 'Installer' AND NOT M.TrackerID IS NULL

OPEN cur

BEGIN TRAN

FETCH NEXT FROM cur INTO @TechID,@TrackerID

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @TechID = 0 RETURN
	IF EXISTS(SELECT * FROM t_Tracker WHERE ID=@TrackerID AND StatusID = 80) BEGIN
		EXEC s_Technician_Tracker_InHands @TechID,@TrackerID
		IF @@ERROR <> 0 BREAK
		PRINT @TechID+':'+@TrackerID+' Done'
	END ELSE PRINT @TechID+':'+@TrackerID
	FETCH NEXT FROM cur INTO @TechID,@TrackerID
END

SELECT * FROM t_Technician_Tracker WHERE TechnicianID > 0

IF @@TRANCOUNT > 0 COMMIT TRAN
